package com.xzy.sms.web.system.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.xzy.sms.web.system.entity.PermUsergroup;
import com.xzy.sms.web.system.entity.Usergroup;

/**
 * 权限与用户组关系DAO
 * @author macong
 * @since 1.0
 * @version 2014-4-1 macong
 */
public interface PermUsergroupDao extends PagingAndSortingRepository<PermUsergroup, String>, JpaSpecificationExecutor<PermUsergroup> {
    
    /**
     * 根据permStr 和tenantId 删除记录
     * 
     * @param permStr permStr
     * @return xx
     */
    @Modifying
    @Query("delete PermUsergroup where permStr=?1")
    public int deleteByPermStr(String permStr);
    
    /**
     * 根据usergroupId 和tenantId 删除记录
     * 
     * @param usergroupId usergroupId
     * @return xx
     */
    @Modifying
    @Query("delete PermUsergroup where usergroup.id=?1")
    public int deleteByUsergroupId(String usergroupId);
    
    /**
     * 查询所有记录
     * @return xx
     */
    @Query("from PermUsergroup")
    public List<PermUsergroup> findAllPermUsergroup();
    
    /**
     * 根据userId查询所有权限串
     * @param userId userId
     * @return xx
     */
    @Query(value = "SELECT DISTINCT d.perm_str FROM pub_user a,pub_usergroup b,pub_usergroup_user c,pub_perm_usergroup d "
        + " WHERE a.user_id=c.user_id AND b.id=c.usergroup_id AND b.id=d.usergroup_id AND a.user_id=?1", nativeQuery = true)
    public List<String> findPermStrsByUserId(String userId);
    
    /**
     * 根据权限特征值查询所有的用户集合
     * @param permStr permStr
     * @return 用户集合
     */
    @Query(value = "SELECT distinct a.user_id,a.name FROM pub_user a,pub_usergroup b,pub_usergroup_user c,pub_perm_usergroup d "
        + " WHERE a.user_id=c.user_id AND b.id=c.usergroup_id AND b.id=d.usergroup_id and d.perm_str = ?1 and a.del_flag = '"
        + Usergroup.DEL_FLAG_NORMAL + "'", nativeQuery = true)
    public List<Object[]> findUsersByPermStr(String permStr);
}
